﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace BO
{
    /// <summary>
    /// Summary description for UserRoleBO
    /// </summary>
    public class UserRoleBO : DBConnectionBO
    {
        public UserRoleBO():base()
        {
        }

        /// <summary>
        /// Get list user role
        /// </summary>
        /// <returns></returns>
        public List<UserRole> GetListUserRole()
        {
            var listUserrole = new List<UserRole>();
            const string query = "SELECT TOP 100 * FROM [PrivateBlog].[dbo].[UserRoles]";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            this.CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var userrole = new UserRole
                    {
                        Id = (int)dataTable.Rows[i][0],
                        RoleName = (dataTable.Rows[i][1] == DBNull.Value) ? string.Empty : dataTable.Rows[i][1].ToString(),
                        Description = (dataTable.Rows[i][2] == DBNull.Value) ? string.Empty : dataTable.Rows[i][2].ToString(),
                        LastDateUpdated = (DateTime)dataTable.Rows[i][3],
                        LastUpdatedBy = (dataTable.Rows[i][4] == DBNull.Value) ? string.Empty : dataTable.Rows[i][4].ToString(),
                        Status = Convert.ToInt32(dataTable.Rows[i][5])
                    };
                listUserrole.Add(userrole);
            }

            return listUserrole;
        }

        /// <summary>
        /// Add new a user role
        /// </summary>
        /// <param name="userRole"></param>
        /// <returns></returns>
        public int AddUserRole(UserRole userRole)
        {
            var query =
                "INSERT INTO [PrivateBlog].[dbo].[UserRoles]([RoleName],[Description],[LastDateUpdated],[LastUpdatedBy],[Status]) VALUES" +
                " ( " +
                "N'" + userRole.RoleName + "', N'" +
                userRole.Description + "', " +
                "GETDATE(), '" +
                userRole.LastUpdatedBy + "', " +
                userRole.Status +
                ")";
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Edit user role
        /// </summary>
        /// <param name="userRole"></param>
        /// <returns></returns>
        public int EditUserRole(UserRole userRole)
        {
            var query = "UPDATE [PrivateBlog].[dbo].[UserRoles]" +
                        "SET RoleName = N'" + userRole.RoleName + "', " +
                        "Description = N'" + userRole.Description + "', " +
                        "LastDateUpdated = GETDATE(), "+
                        "LastUpdatedBy = N'" + userRole.LastUpdatedBy + ", " +            
                        "Status = " + userRole.Status +
                        "WHERE Id = " + userRole.Id;
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Delete a user role
        /// </summary>
        /// <param name="userRole"></param>
        /// <returns></returns>
        public int DeleteUserRole(UserRole userRole)
        {
            var query = "DELETE FROM [PrivateBlog].[dbo].[UserRoles] " +
                        " WHERE Id = " + userRole.Id;
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }
    }
}